package org.compiere.model;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import org.compiere.util.DB;
import org.compiere.util.Env;

public class Callout_FI_Reconciliationact extends CalloutEngine {

	public String SetLines(Properties ctx, int WindowNo, GridTab mTab,
			GridField mField, Object value) {

		int FI_ReconciliationAct_ID = Env.getContextAsInt(ctx, WindowNo,
				"FI_ReconciliationAct_ID");
		
		MReconciliationAct header = new MReconciliationAct(ctx, FI_ReconciliationAct_ID, null);
		if(header!=null){
			Timestamp startDate = header.getDateStart();
			Timestamp endDate = header.getdateend();
			int BPartner = header.getC_BPartner_ID();
			int CMSContract = header.getCMS_Contract_ID();
	
			String StrBPartener = "";
			if(BPartner > 0)
				StrBPartener = " and doc.c_bpartner_id = " + BPartner + "\n";
			
			String sqlRemoveLines = " select FI_ReconciliationActLine_id from adempiere.FI_ReconciliationActLine where FI_ReconciliationAct_id = " + FI_ReconciliationAct_ID;
			
			String sql = 
//					" select doc.dateacct \n" 
//					+ " ,(select dtrl.name ||' '|| doc.documentno ||' от ' from adempiere.c_doctype_trl dtrl where dtrl.c_doctype_id = doc.c_doctype_id and dtrl.ad_language = 'ru_RU') description  \n" 
//					+ " ,(case when d.docbasetype = 'APP' then doc.payamt else 0 end) db \n" 
//					+ " ,(case when d.docbasetype = 'ARR' then doc.payamt else 0 end) cr	\n"
//					+ "	, doc.datetrx \n"
//					+ " from adempiere.c_payment doc	\n"
//					+ " left join adempiere.c_doctype d on d.c_doctype_id = doc.c_doctype_id	\n"
//					+ " where doc.dateacct between '" + startDate + "' and '" + endDate +"'\n"
//					+ 	StrBPartener
//					+ " union all	\n"
//	
//					+ " select doc.dateacct \n" 
//					+ " ,(select dtrl.name ||' '|| doc.documentno ||' от ' from adempiere.c_doctype_trl dtrl where dtrl.c_doctype_id = doc.c_doctype_id and dtrl.ad_language = 'ru_RU') description  \n" 
//					+ " ,(case when d.docbasetype = 'ARI' then doc.grandtotal else 0 end) db \n" 
//					+ " ,(case when d.docbasetype = 'API' then doc.grandtotal else 0 end) cr	\n"
//					+ " ,doc.dateinvoiced "
//					+ " from adempiere.c_invoice doc	\n"
//					+ " left join adempiere.c_doctype d on d.c_doctype_id = doc.c_doctype_id	\n"
//					+ " where doc.dateacct between '" + startDate + "' and '" + endDate +"'\n"
//					+  	StrBPartener;
					"select dateacct, description, dr, cr, dateacct, c_currency_id \n" +
					"from\n" +
					"( \n" +
					"	select doc.dateacct \n" +
					"	 ,(select dtrl.name ||' '|| doc.documentno ||' от ' from adempiere.c_doctype_trl dtrl where dtrl.c_doctype_id = doc.c_doctype_id and dtrl.ad_language = 'ru_RU') description  \n" +
					"	 ,(case when d.docbasetype = 'APP' then doc.payamt else 0 end) dr \n" +
					"	 ,(case when d.docbasetype = 'ARR' then doc.payamt else 0 end) cr	\n" +
					"		, doc.datetrx \n" +
					"	,doc.c_currency_id	\n" + 	
					"	 from adempiere.c_payment doc	\n" +
					"	 left join adempiere.c_doctype d on d.c_doctype_id = doc.c_doctype_id	\n" +
					"	 where doc.dateacct between '" + startDate + "' and '" + endDate +"'\n" +
					"	and doc.posted = 'Y' \n	" +
					 	StrBPartener +
					"	 \n" +
					"	 union all	\n" +
					"	 \n" +
					"	 select doc.dateacct \n" +
					"	 ,(select dtrl.name ||' '|| doc.documentno ||' от ' from adempiere.c_doctype_trl dtrl where dtrl.c_doctype_id = doc.c_doctype_id and dtrl.ad_language = 'ru_RU') description  \n" +
					"	 ,(case when d.docbasetype = 'ARI' then doc.grandtotal else 0 end) dr \n" +
					"	 ,(case when d.docbasetype = 'API' then doc.grandtotal else 0 end) cr	\n" +
					"	 ,doc.dateinvoiced  " +
					"	,doc.c_currency_id	\n" +					
					"  from adempiere.c_invoice doc	\n" +
					"	 left join adempiere.c_doctype d on d.c_doctype_id = doc.c_doctype_id	\n" +
					"	 where doc.dateacct between '" + startDate + "' and '" + endDate +"'\n" +
					"	and doc.posted = 'Y' \n	" +
					 	StrBPartener +
					"	 \n" +
					"	 union all\n" +
					"	 \n" +
					"	select \n" +
					"	header.dateacct, header.description, line.amtsourcedr dr, line.amtsourcecr cr, header.dateacct\n" +
					"	,line.c_currency_id	\n" +					
					"	from adempiere.gl_journal header\n" +
					"	inner join adempiere.gl_journalline line on line.gl_journal_id = header.gl_journal_id \n" +
					"	inner join adempiere.c_validcombination doc on doc.c_validcombination_id = line.c_validcombination_id \n" +
					"	where header.dateacct between '" + startDate + "' and '" + endDate +"'\n" +
					"	and header.posted = 'Y' \n	" +
					 	StrBPartener +
					")main\n" +
					"order by main.dateacct";					
					
			MReconciliationActLine line; 	
					
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			try {
				pstmt = DB.prepareStatement(sqlRemoveLines, null);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					line = new MReconciliationActLine(ctx, rs.getInt(1), null);
					line.delete(true);
				}
				
				pstmt = null;
				rs = null;
				pstmt = DB.prepareStatement(sql, null);
				rs = pstmt.executeQuery();
				Timestamp ts;
				while (rs.next()) {
					line = new MReconciliationActLine(ctx, 0, null);
					line.setFI_ReconciliationAct_ID(FI_ReconciliationAct_ID);
					line.setDateAcct(rs.getTimestamp(1));
					line.setdebet(rs.getBigDecimal(3));
					line.setcredit(rs.getBigDecimal(4));
					
					ts = rs.getTimestamp(5);
					Date d = new Date(ts.getTime());
					SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy");
					String s = format.format(d);
	
					line.setDescription(rs.getString(2) + s);
					line.setC_Currency_ID(rs.getInt("c_currency_id"));
					line.save();
				}
			} catch (SQLException e) {
				log.log(Level.SEVERE, sql, e);
				return e.getLocalizedMessage();
			} catch (Exception e) {
				log.log(Level.SEVERE, sql, e);
				return e.getLocalizedMessage();
			} finally {
				DB.close(rs, pstmt);
				rs = null;
				pstmt = null;
			}
			// if(PayAmt.compareTo(new BigDecimal(0)) == 1){
			//
			// String sql = "UPDATE C_Payment p"
			// + " SET PayAmt= ? "
			// // +
			// "(SELECT COALESCE(SUM(Amount),0) FROM FI_C_PaymentLine pl WHERE p.C_Payment_ID=pl.C_Payment_ID) "
			// + " WHERE C_Payment_ID=?";
			// int no = DB.executeUpdateEx(sql, new Object[]{PayAmt, C_Payment_ID},
			// null);
			//
			// // MPayment payment = new MPayment(ctx, C_Payment_ID, null);
			// // payment.setPayAmt(PayAmt);
			// // payment.save();
			// }
		}
		return "";

	}

}
